package nuc.ss.wl.dao.impl;

import nuc.ss.wl.dao.Special_SituationDao;
import nuc.ss.wl.entity.Special_Situation;
import nuc.ss.wl.entity.Student;
import nuc.ss.wl.entity.Teacher;
import nuc.ss.wl.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class Special_SituationDaoImpl implements Special_SituationDao {
    @Override
    public int save(Special_Situation special_situation) {
        String sql = "insert into special_situation values(?, ?, ?)";
        Object[] params = {
                special_situation.getSno(),
                special_situation.getType(),
                special_situation.getSituation()
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public int delete(int sno) {
        String sql = "delete from special_situation where sno = ?";
        Object[] params = {
                sno
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public int update(Special_Situation special_situation) {
        String sql = "update special_situation set type = ?, situation = ? where sno = ?";
        Object[] params = {
                special_situation.getType(),
                special_situation.getSituation(),
                special_situation.getSno()
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public List<Special_Situation> findAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //声明变量
        Special_Situation special_situation = null;
        List<Special_Situation> special_situationList = new ArrayList<>();
        conn = DBUtil.getConnection();
        String sql = "select * from special_situation";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int sno = rs.getInt("sno");
                String type= rs.getString("type");
                String situation = rs.getString("situation");
                special_situation = new Special_Situation(sno, type, situation);
                special_situationList.add(special_situation);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return special_situationList;
    }

    @Override
    public List<Special_Situation> find(int sno, String type) {
        //声明jdbc变量
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Special_Situation special_situation = null;
        List<Special_Situation> special_situationList = new ArrayList<>();
        try{
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder("select * from special_situation ss where 1=1");
            if(type != null && !"".equals(type)){
                sql.append(" and ss.type like '%" + type + "%'");
            }
            if(sno != 0){
                sql.append(" and ss.sno = " + sno);
            }
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while(rs.next()){
                special_situation = null;
                special_situation.setSno(rs.getInt(1));
                special_situation.setType(rs.getString(2));
                special_situation.setSituation(rs.getString(3));
                special_situationList.add(special_situation);
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return special_situationList;
    }

    @Override
    public Special_Situation findBySno(int sno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //声明变量
        Special_Situation special_situation = null;
        conn = DBUtil.getConnection();
        String sql = "select * from special_situation where sno = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, sno);
            rs = ps.executeQuery();
            while (rs.next()) {
                special_situation = new Special_Situation(rs.getInt(1), rs.getString(2), rs.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return special_situation;
    }
}
